package com.liuhm.dianjuhe.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.liuhm.dianjuhe.entity.User;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 用户mapper
 *
 * @author liuhaomin
 * @date 2020/7/2
 */
public interface UserMapper extends BaseMapper<User> {
    @Select("SELECT " +
            " t1.num, " +
            " t1.gr, " +
            " ST_LongFromGeoHash(gr) lng, " +
            " ST_LatFromGeoHash(gr) lat, " +
            " CONCAT(ST_LongFromGeoHash(gr),\",\",ST_LatFromGeoHash(gr)) lnglat " +
            "FROM " +
            "(SELECT " +
            " count(*) num, " +
            " ST_GeoHash(SUBSTRING_INDEX(`user`.lnglat,\",\",1),SUBSTRING_INDEX(`user`.lnglat,\",\",-1), 4) gr " +
            "FROM " +
            " `user` " +
            " GROUP BY gr) t1")
    List<Map<String,Object>> select();

    @Select("  " +
            "SELECT " +
            " `user`.id,  " +
            " `user`.lnglat, " +
            " SUBSTRING_INDEX(`user`.lnglat,\",\",1) lng, " +
            " SUBSTRING_INDEX(`user`.lnglat,\",\",-1) lat, " +
            " ST_GeoHash(SUBSTRING_INDEX(`user`.lnglat,\",\",1),SUBSTRING_INDEX(`user`.lnglat,\",\",-1), 18) " +
            "FROM " +
            " `user` " +
            " ")
    List<Map<String,Object>> select2();

    @Select("SELECT count(*) from `user` ")
    Integer selectTotal();



}